/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Taxi.db.dao;

import Taxi.db.Database;
import Taxi.model.Taxi;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author BIGBOSS
 */
public class TaxiDAO extends BaseDAO{

    public int save(Taxi taxi) throws SQLException {
        Statement statement = null;
        ResultSet rs;
        try {
            statement = (Statement) Database.getConnection().createStatement();
            return statement.executeUpdate("INSERT INTO taxi VALUES( '"
                    + taxi.getPlateNumber() + "' , " + "'"
                    + taxi.getStartDate() + "' , " + "'"
                    + taxi.getEndDate() + "' , "
                    + +taxi.getStartOdometer() + " , "
                    + +taxi.getEndOdometer() + " , " + "'"
                    + taxi.getOriginalColor() + "' , " + "'"
                    + taxi.getBnewOrSecond() + "' , " + "'"
                    + taxi.getEngineNumber() + "' , " + "'"
                    + taxi.getChasisNumber() + "' , " + "'"
                    + taxi.getGreenPlate() + "' , " + "'"
                    + taxi.getYellowPlate()
                    + "')");
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    public int update(Taxi taxi) throws SQLException {
        System.out.println("UPDATE");
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        return statement.executeUpdate("UPDATE vehicle SET plate_number='"
                + taxi.getPlateNumber() + "', startDate='"
                + taxi.getStartDate() + "', endDate='"
                + taxi.getEndDate() + "',startOdometer="
                + taxi.getStartOdometer() + ",endOdometer="
                + taxi.getEndOdometer() + ",bneworsecond='"
                + taxi.getBnewOrSecond() + "',originalcolor='"
                + taxi.getOriginalColor() + "',enginenumber='"
                + taxi.getEngineNumber() + "',chasisnumber='"
                + taxi.getChasisNumber() + "',greenplate='"
                + taxi.getGreenPlate() + "',yellowplate='"
                + taxi.getYellowPlate() + "' WHERE plateNumber='"
                + taxi.getPlateNumber() + "'");
    }

// sample UPDATE employee SET emp_num =2 WHERE emp_num =1 AND first_name ='Chan' AND last_name='Howard' AND address='129378 askhdao 1' AND birthday='2000-10-20' AND license='qiweju123734';
    public Taxi getTaxiByPlateNumber(String unitnumber) throws SQLException {
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT * FROM VEHICLE WHERE unitnumber='"
                + unitnumber + "'");
        Taxi taxi = new Taxi();
        rs.next();
        taxi.setUnitNumber(rs.getInt("unitnumber"));
        taxi.setPlateNumber(rs.getString("plateNumber"));
        taxi.setStartDate(new Date(rs.getString("startDate")));
        taxi.setEndDate(new Date(rs.getString("endDate")));
        taxi.setStartOdometer(rs.getInt("startOdometer"));
        taxi.setEndOdometer(rs.getInt("end_odometer"));
        taxi.setOriginalColor(rs.getString("origcolor"));
        taxi.setBnewOrSecond(rs.getString("bneworsecond"));
        taxi.setEngineNumber(rs.getString("enginenumber"));
        taxi.setChasisNumber(rs.getString("chasisnumber"));
        taxi.setGreenPlate(rs.getString("greenplate"));
        taxi.setYellowPlate(rs.getString("yellowplate"));
        return taxi;
    }

    public void deleteByPlateNumber(String platenumber) {
        try {
            Statement statement;
            ResultSet rs;
            statement = (Statement) Database.getConnection().createStatement();
            int i = statement.executeUpdate("DELETE FROM VEHICLE WHERE PLATE_NUMBER = '"
                    + platenumber + "'");
        } catch (SQLException insertException) {
            insertException.printStackTrace();
        }
    }

    public List<Taxi> getAllTaxi() throws SQLException {
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT * FROM VEHICLE");
        List<Taxi> taxis = new LinkedList<Taxi>();
        while (rs.next()) {
            Taxi taxi = new Taxi();
            taxi.setUnitNumber(rs.getInt("unitnumber"));
            taxi.setPlateNumber(rs.getString("plateNumber"));
            taxi.setStartDate(toDate(rs.getDate("startDate")));
            taxi.setEndDate(toDate(rs.getDate("endDate")));
            taxi.setStartOdometer(rs.getInt("startOdometer"));
            taxi.setEndOdometer(rs.getInt("endOdometer"));
            taxi.setOriginalColor(rs.getString("originalcolor"));
            taxi.setBnewOrSecond(rs.getString("bneworsecond"));
            taxi.setEngineNumber(rs.getString("enginenumber"));
            taxi.setChasisNumber(rs.getString("chasisnumber"));
            taxi.setGreenPlate(rs.getString("greenplate"));
            taxi.setYellowPlate(rs.getString("yellowplate"));
            taxis.add(taxi);
        }
        return taxis;
    }

    public List<Integer> getAllTaxiUnitNumber() throws SQLException {
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT unitnumber FROM VEHICLE");
        List<Integer> unitNums = new LinkedList<Integer>();
        while (rs.next()) {
            unitNums.add(rs.getInt("unitnumber"));
        }
        return unitNums;
    }
}
